create view vFRDonationTemp as

------------- Select FR single gift and Pledge ------------
------------- select FR matching single gift and matching pledge ------------

select max(P.BT_ID) ID,
(case when max(P.IS_MATCH_GIFT)=1 then max(P.MATCH_GIFT_TRANS_NUM) else max(P.TRANS_NUMBER) end) as OriginalTransNumber,
max(P.TRANS_NUMBER) TransactionNumber,
'' as InvoiceRefNum,
'Fund Raising' as SourceSystem,
max(P.TRANSACTION_DATE) TransactionDate,
(sum(P.AMOUNT) * -1)  as Amount,
max(P.PRODUCT_CODE) Product,
max(P.SOURCE_CODE) Appeal,
max(P.CAMPAIGN_CODE) Campaign,
max(P.OWNER_ORG_CODE) Fund,
(case when max(P.INSTALL_BILL_DATE) is null then 'Gift' else 'Pledge' end) as GiftType,
(case when max(P.IS_MATCH_GIFT)= 1 then 'Matching' else '' end) as MatchOrSoftCredit,
max(Cash_Accounts.ACCOUNT_TYPE) as PaymentType,
'' as AdjustmentFlag
from Trans P
left outer join Cash_Accounts on P.CHECK_NUMBER = Cash_Accounts.CASH_ACCOUNT_CODE
where
P.JOURNAL_TYPE = 'IN' and P.TRANSACTION_TYPE = 'DIST'
and P.SOURCE_SYSTEM='FR'
and P.POSTED >= 2
group by P.BT_ID, P.TRANS_NUMBER, P.OWNER_ORG_CODE, P.PRODUCT_CODE


union

------------ Create CM/DM for single gift and pledge for regular donation or matching Gift -----------

select max(C.BT_ID) ID,
'' as OriginalTransNumber,
max(Invoice.ORIGINATING_TRANS_NUM) TransactionNumber,
'' as InvoiceRefNum,
'' as SourceSystem,
'' as TransactionDate,
(sum(C.AMOUNT) * -1)  as Amount,
max(C.PRODUCT_CODE) Product,
'' as Appeal,
'' as Campaign,
max(C.OWNER_ORG_CODE) Fund,
'' as GiftType,
'' as MatchOrSoftCredit,
'' as PaymentType,
'Yes' as AdjustmentFlag
from Trans C
inner join Invoice on Invoice.REFERENCE_NUM = C.INVOICE_REFERENCE_NUM
where Invoice.SOURCE_SYSTEM='FR'
and ((C.JOURNAL_TYPE = 'CM' and C.TRANSACTION_TYPE = 'DIST')
or  (C.JOURNAL_TYPE = 'DM' and C.TRANSACTION_TYPE = 'DIST'))
and C.POSTED >= 2
group by C.BT_ID, C.TRANS_NUMBER, C.OWNER_ORG_CODE, C.PRODUCT_CODE

GO
GRANT REFERENCES ON  [dbo].[vFRDonationTemp] TO [IMIS]
GRANT SELECT ON  [dbo].[vFRDonationTemp] TO [IMIS]
GRANT INSERT ON  [dbo].[vFRDonationTemp] TO [IMIS]
GRANT DELETE ON  [dbo].[vFRDonationTemp] TO [IMIS]
GRANT UPDATE ON  [dbo].[vFRDonationTemp] TO [IMIS]
GO